package ru.spies.requests;

import java.sql.Date;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Collection;

import javax.servlet.http.HttpServletRequest;

import ru.spies.dbwork.DBWork;
import ru.spies.entities.Agent;
import ru.spies.entities.ITableClass;
import ru.spies.help.MyLogger;


public class DeleteRequestWork extends AbstractRequestWork{
	
	private static DeleteRequestWork statThis=null;
	
	private static final String WHERE=" WHERE ";

	private DeleteRequestWork() {
		
	}
	
	public static DeleteRequestWork getInstance(){
		if (statThis==null){
			statThis= new DeleteRequestWork();
		}	
		return statThis;
	}
	
	public Collection<ITableClass> getCountries(HttpServletRequest request){
		String withCountry=request.getParameter("withCountry");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Country o");
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withCountry!=null){
			queryStr.append(WHERE);
			wasWhere=true;
			queryStr.append("o.id="+request.getParameter("countryName"));
			needAnd=true;
		}
		String withRelation=request.getParameter("withRelation");
		if (withRelation!=null){
			if (needAnd){
				queryStr.append(" and ");
			}
			if (!wasWhere){
				queryStr.append(WHERE);
				wasWhere=true;
			}
			queryStr.append("o.relation='"+request.getParameter("relation")+"'");
		}
		if (!wasWhere){
			return null;
		}
		Collection<ITableClass> countries = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
//		for (ITableClass obj : countries) {
//			Country country = (Country) obj;
//			for (Department dep:country.getDepartments()){
//				for (Agent dep:)
//			}
//		}
		return countries;
	}
	
	public Collection<ITableClass> getAgents(HttpServletRequest request){
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Agent o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("agentName"));
			needAnd=true;
		}
		String withRelation=request.getParameter("withRecruiter");
		if (withRelation!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Agent r ");
			secondQueryPart.append("o.recruiter=r and r.id="+request.getParameter("idRecruiter"));
			needAnd=true;
		}
		String withCnt=request.getParameter("withCountRecruits");
		if (withCnt!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.countRecruits="+request.getParameter("countRecruits"));
			needAnd=true;
		}
		String withDepartment=request.getParameter("withDepartment");
		if (withDepartment!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Department d");
			secondQueryPart.append("o.department=d and d.id="+request.getParameter("idDepartment"));
			needAnd=true;
		}
		String withStatus=request.getParameter("withStatus");
		if (withStatus!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.status="+request.getParameter("status"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> agents = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		for (ITableClass agent : agents) {
			Agent ag=(Agent) agent;
			DBWork.getInstance().removeObjectFromTable(ag.getSiteuser());
		}
		return agents;
	}
	
	public Collection<ITableClass> getDepartments(HttpServletRequest request){
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Department o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("depName"));
			needAnd=true;
		}
		String withCreationDate=request.getParameter("withCreationDate");
		if (withCreationDate!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			Date searchDate = null;
			try {
				searchDate = new Date(DateFormat.getInstance().parse(request.getParameter("creationDate")).getTime());
			} catch (ParseException e) {
				MyLogger.getInstance().getLog().error(MyLogger.EXCEPTION,e);
				return null;
			}
			secondQueryPart.append("o.creationDate='"+searchDate+"'");
			needAnd=true;
		}
		String withCountry=request.getParameter("withCountry");
		if (withCountry!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Country c");
			secondQueryPart.append("o.country=c and c.id="+request.getParameter("idCountry"));
			needAnd=true;
		}
		String withResident=request.getParameter("withResident");
		if (withResident!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Agent r");
			secondQueryPart.append("o.resident=r and r.id="+request.getParameter("idResident"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> departments = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		return departments;
	}
	
	public Collection<ITableClass> getTasks(HttpServletRequest request){
		String withName=request.getParameter("withName");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Task o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withName!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("taskName"));
			needAnd=true;
		}
		String withStatus=request.getParameter("withStatus");
		if (withStatus!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.status='"+request.getParameter("status")+"'");
			needAnd=true;
		}
		String withCountry=request.getParameter("withCountry");
		if (withCountry!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Country c ");
			secondQueryPart.append("o.country=c and c.id="+request.getParameter("idCountry"));
			needAnd=true;
		}
		String withImportance=request.getParameter("withImportance");
		if (withImportance!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Importance i");
			secondQueryPart.append("o.importance=i i.id="+request.getParameter("idImportance"));
			needAnd=true;
		}
		String withTaskType=request.getParameter("withTaskType");
		if (withTaskType!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", TaskType tt");
			secondQueryPart.append("o.taskType=tt and tt.id="+request.getParameter("idTaskType"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> tasks = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		return tasks;
	}
	
	public Collection<ITableClass> getDocuments(HttpServletRequest request){
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Document o");
		StringBuffer secondQueryPart=new StringBuffer();
		String withTextLink=request.getParameter("withTextLink");
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withTextLink!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			secondQueryPart.append("o.id="+request.getParameter("textLink"));
			needAnd=true;
		}
		String withImportance=request.getParameter("withImportance");
		if (withImportance!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			secondQueryPart.append("o.importance="+request.getParameter("idImportance"));
			needAnd=true;
		}
		String withTask=request.getParameter("withTask");
		if (withTask!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Task t");
			secondQueryPart.append("o.task=t and t.id="+request.getParameter("idTask"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> documents = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		return documents;
	}
	
	public Collection<ITableClass> getTaskTypes(HttpServletRequest request){
		String queryStr=new String("SELECT o FROM TaskType o WHERE o.id="+request.getParameter("taskTypeName"));
		Collection<ITableClass> taskTypes = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr);
		return taskTypes;
	}

	public Collection<ITableClass> getImportances(HttpServletRequest request){
		String queryStr=new String("SELECT o FROM Importance o WHERE o.id="+request.getParameter("importanceName"));
		Collection<ITableClass> importances = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr);
		return importances;
	}
	
	public Collection<ITableClass> getAgentsAndTasks(HttpServletRequest request){
		String withAgent=request.getParameter("withAgent");
		StringBuffer queryStr=new StringBuffer("SELECT o FROM Agentsandtask o");
		StringBuffer secondQueryPart=new StringBuffer();
		boolean needAnd=false;
		boolean wasWhere=false;
		if (withAgent!=null){
			secondQueryPart.append(WHERE);
			wasWhere=true;
			queryStr.append(", Agent a ");
			secondQueryPart.append("o.agent=a and a.id="+request.getParameter("idAgent"));
			needAnd=true;
		}
		String withTask=request.getParameter("withTask");
		if (withTask!=null){
			if (needAnd){
				secondQueryPart.append(" and ");
			}
			if (!wasWhere){
				secondQueryPart.append(WHERE);
				wasWhere=true;
			}
			queryStr.append(", Task t");
			secondQueryPart.append("o.task=t and t.id="+request.getParameter("idTask"));
		}
		if (!wasWhere){
			return null;
		}
		queryStr.append(secondQueryPart);
		Collection<ITableClass> agentsandtasks = (Collection<ITableClass>) DBWork.getInstance().getTableObjects(queryStr.toString());
		return agentsandtasks;
	}
	
}
